package cn.qayy.controller;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.alibaba.fastjson.JSONObject;

import cn.qayy.constants.Constant;
import cn.qayy.dto.Msg;
import cn.qayy.dto.QueryVO;
import cn.qayy.po.KdbNssLogPlans;
import cn.qayy.utils.DataSourceUtils;
import cn.qayy.utils.ResonseUtils;
import cn.qayy.utils.StringUtil;

/**
 * 流向费用提单
 * 
 * @author jobs1127
 *
 */
@Controller
@RequestMapping(value = "/tidan")
@SuppressWarnings("all")
public class PlanTidanController {
	public static void main(String[] args) {
		
	}
	
	/***
	 * 优化后的提单方式，全国提单
	 */
	@RequestMapping("/all")
	@ResponseBody
	public Msg systemTidang(HttpServletResponse response) {//TODO 系统提单
		Msg msg = null;
		try {
			long start = System.currentTimeMillis();
			tidan("");
			long end = System.currentTimeMillis();
			msg = new Msg(Constant.SUCESSFUL_CODE, "全国提单已完成!");
		} catch (Exception e) {
			e.printStackTrace();
			msg = new Msg(Constant.FAIL_CODE,Constant.FAIL_MSG+e.getMessage());
		}
		return msg;
	}
	/***
	 * 优化后的提单方式，根据片区提单
	 */
	@RequestMapping("/bySpace")
	@ResponseBody
	public Msg systemTidangBySpace(QueryVO vo) {//TODO 系统提单
		Msg msg = null;
		List<String> logspaces = StringUtil.strDaoHaoId2List(vo.getSpaces());
		try { 
			// 拿到所有提单的片区
			StringBuffer sp = new StringBuffer();
			if (!logspaces.isEmpty()) {
				sp.append(" and space in (");
				for (String s : logspaces) {
					sp.append("'" + s + "',");
				}
				sp.deleteCharAt(sp.length()-1);
				sp.append(") ");
			}
			//System.out.println("sp="+sp.toString());
			long start = System.currentTimeMillis();
			tidan(sp.toString());
			long end = System.currentTimeMillis(); 
			msg = new Msg(Constant.SUCESSFUL_CODE, "根据片区提单完成，花时："+(end-start)/1000+"秒");
		} catch (Exception e) {
			e.printStackTrace();
			msg = new Msg(Constant.FAIL_CODE,Constant.FAIL_MSG+e.getMessage());
		}
		return msg;
	}
	
	/***
	 * 对流向进行押->待操作
	 * 流向满足提单的基本条件：
	 * 0、【 纯销】
	 * 1、【当年以及上一年】
	 * 2、【 非毕】
	 * 3、协议存在 且【协议为5状态】
	 * 5、模式为：小包、虚拟小包、自营
	 * 
	 * 提单算法：
	 * 4、【 账单要符合要求】（由结算方式确定）
	 * 6、T1、T3渠道流向需满足最低起付线标准，其他无此要求
	 * 7、若结算方式包含押批，则需满足押批算法
	 * @throws SQLException 
	 */
	public void tidan(String spaces) throws SQLException {
		String andSql = "";
		if(StringUtil.isNotBlank(spaces)) {
			andSql = spaces;
		} 
		String bill = getCurrentBill();
		//当前账单 201805
		int currentBill = getBill(bill);
		//当前账单月 05
		int currentBillMonth = getBillMonth(bill);
		int T1_line = 50;
		int T3_line = 20;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
		String year = sdf.format(new Date());//当前年
		String pyear = (Integer.parseInt(year)-1)+"";//上一年
		//准备提单的满足基本条件的所有流向
		List<KdbNssLogPlans> list = new ArrayList<KdbNssLogPlans>(20000);
		//满足基本条件后，并满足账单要求，并满足最低起付线的所有流向
		List<KdbNssLogPlans> list_line = new ArrayList<KdbNssLogPlans>(20000);
		//本次提单所有通过条件的合格的提单流向id
		List<Integer> tidanIds = new ArrayList<Integer>(10000);
		//所有通过条件的合格的提单流向对象
		Map<String,KdbNssLogPlans> PlansMap = new HashMap<String,KdbNssLogPlans>(10000);
		
		//本次提单所有不满足最低起付线的流向id
		List<Integer> noMeetLineIds = new ArrayList<Integer>(10000);
		
		QueryRunner query = new QueryRunner(DataSourceUtils.getDataSource());
		
		/***
		 * 每个协议的最低起付线sql
		 * 计算每个协议的账单日前的连续3个月的月均，有效月，总和
		 */
		String sql_line = "select clientpolicyId pid,sum(count) sum, " + 
				"if(" + 
				"IF(PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(min(sdate),'%Y%m'))>12,12,PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(min(sdate),'%Y%m'))) " + 
				"> 3,3,IF(PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(min(sdate),'%Y%m'))>12,12,PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(min(sdate),'%Y%m'))) " + 
				") month, " + 
				"sum(count)/ " + 
				"if( " + 
				"IF(PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(min(sdate),'%Y%m'))>12,12,PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(min(sdate),'%Y%m'))) " + 
				"> 3,3,IF(PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(min(sdate),'%Y%m'))>12,12,PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(min(sdate),'%Y%m'))) " + 
				") avg " + 
				"from kdb_nss_log_plans lg where judge='纯销' " + 
				"and date_format(sdate,'%Y/%m') " + 
				"between date_format(DATE_ADD(NOW(),INTERVAL-3 MONTH),'%Y/%m')  " + 
				"and date_format(DATE_ADD(NOW(),INTERVAL-1 MONTH),'%Y/%m') " + 
				"and lg.`clientpolicyId`!=0 " 
				+ andSql +
				" GROUP BY lg.`clientpolicyId`";
		List<Object[]> lineObjs = query.query(sql_line, new ArrayListHandler());
		/***
		 * 最低起付线相关的，存放每个协议的的最近连续3个月的月均纯销量，合作不足3个月按实际合作月计算有效月
		 * 最近连续3个月：比如：Y2018M05,5月5日提单，则连续的3个月为：2、3、4
		 */
		Map<String,Float> lineMap = new HashMap<String,Float>(20000);
		for(Object[] obj:lineObjs) {
			String pid = obj[0]==null?"0":obj[0].toString();
			Float avg = obj[3]==null?0:Float.parseFloat(obj[3].toString());
			lineMap.put(pid, avg);
		}
		
		//每个协议的安全库存
		String safe_line = "SELECT lg.`clientpolicyId` pid, " + 
				"SUM(lg.count) sum, " + 
				"if((PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(min(sdate),'%Y%m')))>12,12,(PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(min(sdate),'%Y%m')))) month, " + 
				"SUM(lg.count) " + 
				"/ " + 
				"if((PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(min(sdate),'%Y%m')))>12,12,(PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(min(sdate),'%Y%m'))))*(1/3) safe_count " + 
				"FROM `kdb_nss_log_plans` lg  " + 
				"WHERE lg.`clientpolicyId`!=0 AND lg.year IN('"+pyear+"','"+year+"') " 
				+ andSql +
				" GROUP BY lg.`clientpolicyId`";
		List<Object[]> safeObjs = query.query(safe_line, new ArrayListHandler());
		/***
		 * 某个协议的合作以来月均的1/3，合作月为当前日期-第一笔流向的日期+1
		 * 存放每个协议的安全库存
		 */
		Map<String,Float> safeMap = new HashMap<String,Float>(20000);
		for(Object[] obj:safeObjs) {
			String pid = obj[0]==null?"0":obj[0].toString();
			Float avg = obj[3]==null?0:Float.parseFloat(obj[3].toString());
			safeMap.put(pid, avg);
		}
		
		//获取准备提单的所有流向sql
		String sql = "select * from kdb_nss_log_plans " 
				+ " where judge='纯销' and status1 not like '%毕%' "
				+ " and year in ('"+pyear+"','"+year+"') " 
				+ " and mode in('小包','虚拟小包','自营') "
				+ andSql 
				+ " order by DATE_FORMAT(sdate,'%Y/%m/%d') desc ";
		list.addAll(query.query(sql, new BeanListHandler<KdbNssLogPlans>(KdbNssLogPlans.class)));
		/***
		 * 迭代准备提单的所有流向
		 */
		for(KdbNssLogPlans p:list) {
			String qudao = p.getTerminaltype();//渠道 T1~T4
			//根据当前流向的协议id，获取其提单月前连续3个月的平均量，若没拿到则为0，意味着该流向不满足最低起付线标准
			Float lavg = lineMap.get(p.getClientpolicyId())==null?0:lineMap.get(p.getClientpolicyId());
			/***
			 * 备案通过后的神舟客户不受最低起付线的限制
			 */
			if(p.getClient().equals("林祥黔") || p.getClient().equals("任家忠")) {
				list_line.add(p);
			} else {
				if("T1".equals(qudao)) {//需要满足最低起付线 T1
					if(lavg >= T1_line) {
						list_line.add(p);
					} else {
						if(p.getClientpolicyId() != null && !p.getClientpolicyId().equals("0") && !p.getClientpolicyId().equals("-1")) {
							String b = p.getZhangdan1();//每笔流向的账单
							if(b!=null && b.contains("Y")) {
								int logBill = getBill(b);//每笔流向的账单
								if(logBill <= currentBill) {//满足账单要求时才能判断是否最低起付线
									noMeetLineIds.add(p.getId());
								}
							}
						}
					}	
				} else if("T3".equals(qudao)){//需要满足最低起付线 T3
					if(lavg >= T3_line) {
						list_line.add(p);
					} else {
						if(p.getClientpolicyId() != null && !p.getClientpolicyId().equals("0") && !p.getClientpolicyId().equals("-1")) {
							String b = p.getZhangdan1();//每笔流向的账单
							if(b!=null && b.contains("Y")) {
								int logBill = getBill(b);//每笔流向的账单
								if(logBill <= currentBill) {//满足账单要求时才能判断是否最低起付线
									noMeetLineIds.add(p.getId());
								}
							}
						}
					}	
				} else { //其他渠道 无最低起付线要求
					list_line.add(p);
				}
			}
		}
		for(String key:safeMap.keySet()) {
			String flag = "0";
			float LastSum = 0;//最后一笔流向求和
			for(KdbNssLogPlans p:list_line) {
				//协议是否合格
				boolean policyOK = false;
				if(StringUtil.isNotBlank(p.getPolicystatus())) {
					try {
						int status = Integer.parseInt(p.getPolicystatus());
						//6状态表示已经复核备案过了。
						if(status >= 5) {
							policyOK = true;
						}
					} catch (NumberFormatException e) {
						policyOK = false;
					}
				}
				/**保证有协议id，并且协议状态为5以上状态*/
				if(key.equals(p.getClientpolicyId()) && policyOK) {
					//满足最低起付线后，若结算方式包含【押批】再去满足押批算法
					if(p.getJsmode().contains("押批")) {
						LastSum += Float.parseFloat(p.getCount());
						/***
						 * 最后一笔的求和小于安全量，PlansMap不存，
						 * 如果最后一笔求和一旦大于安全量后，PlansMap开始存放流向，但是最后一笔流向要押住，故flag=0来标识
						 */
						if(safeMap.get(key)<=LastSum) {
							PlansMap.put(flag, p);
							flag=UUID.randomUUID().toString();
						}
					} else {
						PlansMap.put(UUID.randomUUID().toString(), p);
					}
				}
			}
		}
		//System.out.println("提单流向id：开始");
		for(String key:PlansMap.keySet()) {
			if(!"0".equals(key)) {
				KdbNssLogPlans p = PlansMap.get(key);
				String b = p.getZhangdan1();//每笔流向的账单
				if(b!=null && b.contains("Y")) {//账单不为空的情况下提单
					int logBill = getBill(b);//每笔流向的账单
					if(logBill <= currentBill) {//满足账单要求时才可以提单
						tidanIds.add(p.getId());
					}
				}
			}
		}
//		System.out.println("提单流向条数："+tidanIds.size());
//		System.out.println("提单流向id：结束*************");
		Object[][] params = new Object[tidanIds.size()][];
		for(int i=0;i<tidanIds.size();i++) {
			params[i] = new Object[] {tidanIds.get(i)};
		}
		String update = "update kdb_nss_log_plans set status1='待',tidanfangshi='系统提单',moneybill1='"+bill+"' where id=? and status1 not like '%毕%' ";
		query.batch(update, params);
		
		/**更新流向，把不满足最低起付线的流向标识出来*/
		Object[][] params_noMeetLine = new Object[noMeetLineIds.size()][];
		for(int i=0;i<noMeetLineIds.size();i++) {
			params_noMeetLine[i] = new Object[] {noMeetLineIds.get(i)};
		}
		String update_noMeetLine = "update kdb_nss_log_plans set status1='押(未到起付线)',tidanfangshi='系统提单',moneybill1='"+bill+"' where id=? and status1 not like '%毕%' ";
		query.batch(update_noMeetLine, params_noMeetLine);
		
		/***
		 * 财务要求，票据方案：方案1，1状态为待，则3状态为待
		 */
		String sql3status = "update kdb_nss_log_plans set status3 = '待' where kaipiaofangshi='方案1' and status1 = '待' and status3 not like '%毕%' ";
		query.update(sql3status);
		
		/***
		 * 非毕的负数纯销、且不是自然流向全部为待
		 */
		String update_fushu = "update kdb_nss_log_plans set status1='待',tidanfangshi='系统提单',moneybill1='"+bill+"' where 1=1 and judge='纯销' and count < 0 and client not like '%QA%' and status1 not like '%毕%' ";
		query.update(update_fushu);
		System.out.println("提单顺利结束");
	}
	/***
	 * 
	 * @param i 月份
	 * @return 得到前一个月 后计算当前年
	 */
	private static int getYearByMonth(int i) {
		Calendar calendar = Calendar.getInstance(); 
		calendar.add(Calendar.DATE, -i);    //得到前一天 
		calendar.add(Calendar.MONTH, -i);    //得到前一个月
		int year = calendar.get(Calendar.YEAR); 
		return year;
	}
	/**
	 * 
	 * @param i 月份 i=0时为当前月
	 * @return 得到前一个月 
	 */
	private static int getPreMonth(int i) {
		Calendar calendar = Calendar.getInstance(); 
		calendar.add(Calendar.DATE, -i);    //得到前一天 
		calendar.add(Calendar.MONTH, -i);    //得到前一个月
		int year = calendar.get(Calendar.YEAR); 
		int month = calendar.get(Calendar.MONTH)+1; 
		return month;
	}
	/***
	 * 
	 * @param bill "Y2018M05"
	 * @return 201805
	 */
	public static int getBill(String bill) {
		String year = bill.substring("Y".length(),"2018M".length());
		String mouth = bill.substring("Y2018M".length(),bill.length());
		String b = year+mouth;
		return Integer.parseInt(b);
	}
	/***
	 * 
	 * @param bill "Y2018M05"
	 * @return
	 */
	public static int getBillMonth(String bill) {
		String mouth = bill.substring("Y2018M".length(),bill.length());
		return Integer.parseInt(mouth);
	}
	private static String getCurrentBill() {
		SimpleDateFormat dateFormaty = new SimpleDateFormat("yyyy");
		SimpleDateFormat dateFormatm = new SimpleDateFormat("MM");
		String currentyear = dateFormaty.format(new Date());
		String currentmouth = dateFormatm.format(new Date());
		String year = "";
		String mouth = "";
		year = "Y" + currentyear;
		if (new Integer(currentmouth) < 10) {
			mouth = "M0" + (new Integer(currentmouth));
		} else {
			mouth = "M" + (new Integer(currentmouth));
		}
		return year + mouth;
	}
}
